﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Demo
{
    public partial class SaveFile : Form
    {
        public SaveFile()
        {
            InitializeComponent();
        }

        private void SaveFile_Load(object sender, EventArgs e)
        {
            load_List();
        }

        private void load_List()
        {
            comboBox1.Items.Clear();
            SqlConnection conn = new SqlConnection(
                @"Data Source=BU\SQLEXPRESS;Initial Catalog=BlobsDatabase;Integrated Security=True");
            SqlCommand com = new SqlCommand("select BlobFileName from BlobsTable", conn);
            conn.Open();
            SqlDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                comboBox1.Items.Add(reader["BlobFileName"]);
            }
            reader.Close();
            conn.Close();
        }    

        private void btn_Save_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Title = "Select a File to Save to Database";
            openFile.ShowDialog();
            string filePath = openFile.FileName;
            //A stream of bytes that represnts the binary file
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            //The reader reads the binary data from the file stream
            BinaryReader reader = new BinaryReader(fs);
            //Bytes from the binary reader stored in BlobValue array
            byte[] BlobValue = reader.ReadBytes((int)fs.Length);
            string BlobFileName = filePath.Substring(filePath.LastIndexOf("\\") + 1);
            fs.Close();
            reader.Close();
            SqlConnection BlobsDatabaseConn = new SqlConnection(@"Data Source=BU\SQLEXPRESS;Initial Catalog=BlobsDatabase;Integrated Security=True");
            SqlCommand SaveBlobeCommand = new SqlCommand();
            SaveBlobeCommand.Connection = BlobsDatabaseConn;
            SaveBlobeCommand.CommandType = CommandType.Text;
            SaveBlobeCommand.CommandText = "INSERT INTO BlobsTable(BlobFileName, BlobFile)" +
                "VALUES (@BlobFileName, @BlobFile)";
            SqlParameter BlobFileNameParam = new SqlParameter("@BlobFileName", SqlDbType.VarChar);
            SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.Binary);
            SaveBlobeCommand.Parameters.Add(BlobFileNameParam);
            SaveBlobeCommand.Parameters.Add(BlobFileParam);
            BlobFileNameParam.Value = filePath.Substring(filePath.LastIndexOf("\\") + 1);
            BlobFileParam.Value = BlobValue;
            try
            {
                SaveBlobeCommand.Connection.Open();
                SaveBlobeCommand.ExecuteNonQuery();

                MessageBox.Show(BlobFileNameParam.Value.ToString() + " saved to database.", "BLOB Saved",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
            finally
            {
                SaveBlobeCommand.Connection.Close();
                load_List();
            }        
        }

        private void btn_SaveAs_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog saveFile = new FolderBrowserDialog();
            saveFile.Description = "Select a folder to save your file to";
            saveFile.ShowDialog();
            string SavePath = saveFile.SelectedPath;
            SqlConnection SaveConn = new SqlConnection(
                @"Data Source=BU\SQLEXPRESS;Initial Catalog=BlobsDatabase;Integrated Security=True");
            SqlCommand SaveCommand = new SqlCommand();
            SaveCommand.CommandText =
                "Select BlobFileName, BlobFile from BlobsTable where BlobFileName = @BlobFileName";
            SaveCommand.Connection = SaveConn;
            SaveCommand.Parameters.Add("@BlobFileName", SqlDbType.VarChar).Value = comboBox1.Text;
            //the index number to write bytes to
            long CurrentIndex = 0;
            //the number of bytes to store in the array
            int BufferSize = 100;
            //The Number of bytes returned from GetBytes() method
            long BytesReturned;
            //A byte array to hold the buffer
            byte[] Blob = new byte[BufferSize];
            SaveCommand.Connection.Open();
            SqlDataReader reader = SaveCommand.ExecuteReader(CommandBehavior.SequentialAccess);
            while (reader.Read())
            {
                FileStream fs = new FileStream(SavePath + "\\" + reader["BlobFileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);
                BinaryWriter writer = new BinaryWriter(fs);
                //reset the index to the beginning of the file
                CurrentIndex = 0;
                BytesReturned = reader.GetBytes(
                    1, //the BlobsTable column index
                    CurrentIndex, // the current index of the field from which to begin the read operation
                    Blob, // Array name to write the buffer to
                    0, // the start index of the array
                    BufferSize // the maximum length to copy into the buffer
                    );
                while (BytesReturned == BufferSize)
                {
                    writer.Write(Blob);
                    writer.Flush();
                    CurrentIndex += BufferSize;
                    BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);
                }
                writer.Write(Blob, 0, (int)BytesReturned);
                writer.Flush(); writer.Close();
                fs.Close();
            }
        }
    }
}
